/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package df.bean.report;

/**
 *
 * @author
 */
public class ReportQuery {

    String hospital_code;
    String from_date;
    String to_date;
    String doctor_code;
    String doctor_profile_code;
    String doctor_profile_code_to;
    String doctor_category;
    String payor_code;
    String order_item;
    String order_item_category;
    String doctor_department;
    String transaction_type;
    String admission_type;
    String hn_no;
    String invoice_no;
    String doc_type;
    String transaction_module;
    String year;
    String month;
    String payment_mode;
    String doctor_type;

    public void setHospitalCode(String n){
        this.hospital_code = n;
    }
    public void setFromDate(String n){
        this.from_date = n;
    }
    public void setToDate(String n){
        this.to_date = n;
    }
    public void setDoctorProfileCode(String n){
    	this.doctor_profile_code = n;
    }
    public void setPayorCode(String n){
    	this.payor_code = n;
    }
    public void setDoctorProfileCodeTo(String n){
    	this.doctor_profile_code_to = n;
    }
    public void setDoctorCode(String n){
        this.doctor_code = n;
    }
    public void setDoctorCategory(String n){
        this.doctor_category = n;
    }
    public void setOrderItem(String n){
        this.order_item = n;
    }
    public void setOrderItemCategory(String n){
        this.order_item_category = n;
    }
    public void setDoctorDepartment(String n){
        this.doctor_department = n;
    }
    public void setTransactionType(String n){
        this.transaction_type = n;
    }
    public void setHn_No(String n){
    	this.hn_no = n;
    }
    public void setInvoiceNo(String n){
    	this.invoice_no = n;
    }
    public void setAdmissionType(String n){
        this.admission_type = n;
    }
    public void setDocType(String n){
        this.doc_type = n;
    }
    public void setTransactionModule(String n){
        this.transaction_module = n;
    }
    public void setYear(String n){
    	this.year = n;
    }
    public void setMonth(String n){
    	this.month = n;
    }
    public void setPaymentMode(String n){
    	this.payment_mode = n;
    }
    public void setDoctorType(String n){
    	this.doctor_type = n;
    }
    public String getReport(String n){
        String qr = "";
        if(n.equals("SummaryDaily")){
            qr = summaryDaily();
        }
        if(n.equals("ImportTransaction")){
            qr = importTransaction();
        }
        if(n.equals("DailyChecklistDoctorOrder")){
            qr = dailyChecklistInvoiceOrder();
        }
        if(n.equals("DailyChecklistInvoiceOrder")){
            qr = dailyChecklistInvoiceOrder();
        }
        if(n.equals("SummaryRevenueByDetail")){
            qr = revenueDetail();
        }
        if(n.equals("SummaryDFUnpaidByDetailAsOfDate")){
        	qr = unpaidDetail();
        }
        if(n.equals("SummaryRevenueByDoctor")){
        	qr = sumRevenue();
        }
        if(n.equals("SummaryMonthlyDetailChecklist")){
        	qr = sumDetailGLPayment();
        }
        if(n.equals("GLAccru")){
        	qr = sumDetailGLAccrue();
        }
        
        return qr;
    }
    
    /** 
     *  TODO By : Mr.sarunyoo keawsopa
     *  Date Modify : 27 January 2012
     *  Status :  complete 
     * @return String 
     */
    private String sumDetailGLAccrue() {
		return "SELECT  "+
		"SD.YYYY AS 'YYYY', SD.MM AS 'MM' , " +
		"SD.DOCTOR_CODE AS 'DOCTOR_CODE' , " +
		"DR.NAME_THAI AS 'NAME_THAI' , " +
		"SD.INVOICE_NO AS 'INVOICE_NO', "+
		"SD.INVOICE_DATE AS 'INVOICE_DATE' , "+
		"SD.HN_NO AS 'HN_NO',"+
		"SD.PATIENT_NAME AS 'PATIENT_NAME', "+
		"SD.VERIFY_DATE AS 'VERIFY_DATE', "+
		"SD.VERIFY_TIME AS 'START_TIME', "+
		"CASE WHEN SD.AMOUNT_AFT_DISCOUNT < SD.DR_AMT THEN SD.DR_AMT ELSE SD.AMOUNT_AFT_DISCOUNT END AS AMOUNT_AFT_DISCOUNT  , "+
		"SD.DR_AMT AS 'DR_AMT',"+
		"SD.GUARANTEE_NOTE,"+
		"SD.DR_TAX_402+SD.DR_TAX_406+SD.DR_TAX_401 AS 'DR_TAX_AMT', "+
		"SD.ORDER_ITEM_CODE AS 'ORDER_ITEM_CODE', "+
		"SD.ORDER_ITEM_CATEGORY_CODE, "+
		"OI.ACCOUNT_CODE, "+ //  NOT FOUND 'OI' VARIABLE 
		"SD.FIRST_LOCATION_CODE, " +
		"SD.PATIENT_LOCATION_CODE, " +
		"SD.RECEIPT_LOCATION_CODE, " +
		"SD.DOCTOR_DEPARTMENT_CODE, "+
		"SD.DOCTOR_CATEGORY_CODE , "+
		"DP.CODE AS PROFILE_CODE , "+
		"DP.NAME_THAI AS PROFILE_NAME, "+
		"SD.LINE_NO AS 'LINE_NO'"+
        //"CASE WHEN (SD.FIRST_DEPARTMENT_CODE = '' OR SD.FIRST_DEPARTMENT_CODE IS NULL) " +
		//"THEN SD.DOCTOR_DEPARTMENT_CODE ELSE SD.FIRST_DEPARTMENT_CODE " +
		//"END AS FIRST_DEPARTMENT_CODE, " +
		"FROM TRN_DAILY SD LEFT OUTER JOIN DOCTOR DR ON (SD.DOCTOR_CODE = DR.CODE AND SD.HOSPITAL_CODE = DR.HOSPITAL_CODE) "+
		"LEFT OUTER JOIN DOCTOR_PROFILE DP ON (DR.DOCTOR_PROFILE_CODE = DP.CODE AND DR.HOSPITAL_CODE = DP.HOSPITAL_CODE) "+
		"LEFT OUTER JOIN ORDER_ITEM OI ON (SD.ORDER_ITEM_CODE = OI.CODE AND SD.HOSPITAL_CODE = OI.HOSPITAL_CODE)"+ // NEW LINE 
		"WHERE (SD.INVOICE_DATE BETWEEN '"+this.from_date+"' AND '"+this.to_date+"') AND "+
		"SD.HOSPITAL_CODE LIKE '"+this.hospital_code+"' AND DP.CODE LIKE '"+this.doctor_profile_code+"' AND "+
		"SD.PAYOR_OFFICE_CODE LIKE '"+this.payor_code+"' AND "+
		"SD.ACTIVE = '1' AND DR.ACTIVE = '1' AND SD.ORDER_ITEM_ACTIVE = '1' AND "+
		"(SD.BATCH_NO = '' OR SD.BATCH_NO IS NULL) AND "+
		"SD.INVOICE_TYPE <> 'ORDER' AND SD.IS_PAID <> 'N' "+
		"ORDER BY DP.CODE,SD.DOCTOR_CODE,SD.INVOICE_DATE";
	}
	
    /**
     * TODO BY : Mr.sarunyoo keawsopa
     * Date Modify : 27 January 2012
     * Status : complete 
     * @return String
     */
    private String sumDetailGLPayment() {
		return "SELECT "+
		"SD.YYYY AS 'YYYY', SD.MM AS 'MM', " +
		"SD.DOCTOR_CODE AS 'DOCTOR_CODE' , " +
		"DR.NAME_THAI AS 'NAME_THAI', " +
		"SD.INVOICE_NO AS 'INVOICE_NO', " +
		"SD.INVOICE_DATE AS 'INVOICE_DATE', " +
		"SD.HN_NO AS 'HN_NO' , " +
		"SD.PATIENT_NAME AS 'PATIENT_NAME', "+
		"SD.VERIFY_DATE AS 'VERIFY_DATE',"+
		"SD.VERIFY_TIME AS 'START_TIME'," +	
		"CASE WHEN SD.AMOUNT_AFT_DISCOUNT < SD.DR_AMT THEN SD.DR_AMT ELSE SD.AMOUNT_AFT_DISCOUNT END AS AMOUNT_AFT_DISCOUNT , "+
		"SD.DR_AMT AS 'DR_AMT', "+
		"SD.GUARANTEE_NOTE , "+
		"SD.DR_TAX_402+SD.DR_TAX_406+SD.DR_TAX_401 AS 'DR_TAX_AMT', "+
		"SD.ORDER_ITEM_CODE AS 'ORDER_ITEM_CODE', " +
		"OI.DESCRIPTION_THAI AS 'DESCRIPTION_THAI', "+  // NEW LINE MR.SARUNYOO 
		"SD.ORDER_ITEM_CATEGORY_CODE, " +
		"OI.ACCOUNT_CODE, " + // NOT FOUND "OI" VARIABLE ERROR 
		"SD.FIRST_LOCATION_CODE, " +
		"SD.PATIENT_LOCATION_CODE, " +
		"SD.RECEIPT_LOCATION_CODE," +
		"SD.DOCTOR_DEPARTMENT_CODE, " +
		"SD.DOCTOR_CATEGORY_CODE, " +
		"DP.CODE AS PROFILE_CODE," +
		"DP.NAME_THAI AS PROFILE_NAME, "+	
		"SD.LINE_NO AS 'LINE_NO' " +
		
        //"CASE WHEN (SD.FIRST_DEPARTMENT_CODE = '' OR SD.FIRST_DEPARTMENT_CODE IS NULL) " +
		//"THEN SD.DOCTOR_DEPARTMENT_CODE ELSE SD.FIRST_DEPARTMENT_CODE " +
		//"END AS FIRST_DEPARTMENT_CODE, " +
	
		"FROM TRN_DAILY SD LEFT OUTER JOIN DOCTOR DR ON (SD.DOCTOR_CODE = DR.CODE AND SD.HOSPITAL_CODE = DR.HOSPITAL_CODE) "+
		"LEFT OUTER JOIN DOCTOR_PROFILE DP ON (DR.DOCTOR_PROFILE_CODE = DP.CODE AND DR.HOSPITAL_CODE = DP.HOSPITAL_CODE) "+
		"LEFT OUTER JOIN ORDER_ITEM OI ON (SD.ORDER_ITEM_CODE = OI.CODE AND SD.HOSPITAL_CODE = OI.HOSPITAL_CODE )"+ // NEW LINE MR.SARUNYOO
		"WHERE (DP.CODE BETWEEN '"+this.doctor_profile_code+"' AND '"+this.doctor_profile_code_to+"') "+
		"AND SD.YYYY = '"+this.year+"' "+
		"AND SD.MM = '"+this.month+"' "+
		"AND SD.HOSPITAL_CODE = '"+this.hospital_code+"' "+
		"AND SD.ACTIVE = '1' AND SD.ORDER_ITEM_ACTIVE = '1' "+
		"AND SD.COMPUTE_DAILY_DATE is not null AND SD.COMPUTE_DAILY_DATE != '' "+
		"AND (SD.PAY_BY_CASH='Y' OR SD.PAY_BY_AR='Y' OR SD.PAY_BY_DOCTOR='Y' OR SD.PAY_BY_PAYOR='Y' OR SD.PAY_BY_CASH_AR='Y') "+
		"AND SD.IS_PAID = 'Y' "+
		"ORDER BY DP.CODE, SD.DOCTOR_CODE, SD.VERIFY_DATE";
	}
	
	/**
	 * TODO BY :  Mr.sarunyoo keawsopa
	 * Date Modify:  25 January 2012
	 * Status : complete
	 * @return String 
	 */
    private String dailyChecklistInvoiceOrder(){
    	return "SELECT S.HOSPITAL_CODE , S.INVOICE_NO , S.INVOICE_DATE , S.DOCTOR_CODE , "
    	+" D.NAME_THAI , S.HN_NO , S.PATIENT_NAME , S.PAYOR_OFFICE_NAME , S.ORDER_ITEM_DESCRIPTION , "
    	+" S.VERIFY_DATE , S.VERIFY_TIME , S.AMOUNT_AFT_DISCOUNT , S.DR_AMT ,  S.NOTE , "
    	+" S.HP_AMT , S.NOR_ALLOCATE_PCT , S.INVOICE_TYPE , S.DOCTOR_DEPARTMENT_CODE ," 
    	+" S.ORDER_ITEM_CODE , S.ORDER_ITEM_CATEGORY_CODE , OI.ACCOUNT_CODE , S.FIRST_LOCATION_CODE,"
    	+" S.PATIENT_LOCATION_CODE , S.RECEIPT_LOCATION_CODE , "
    	+" SUBSTRING(S.ORDER_ITEM_CODE+':'+S.ORDER_ITEM_DESCRIPTION,0,40) AS ORDER_ITEM,"
    	+" S.PRIVATE_DOCTOR , S.NOR_ALLOCATE_AMT , S.GUARANTEE_NOTE , " 
    	+" S.DR_TAX_400+S.DR_TAX_401+S.DR_TAX_402+S.DR_TAX_406 AS DR_TAX_AMT,"
    	+" S.TAX_TYPE_CODE , "
    	+" SUBSTRING(S.DOCTOR_CODE+':'+D.NAME_THAI,0,35) AS DOCTOR_AND_NAME, "
    	+" S.DOCTOR_CATEGORY_CODE , S.LINE_NO , S.EPISODE_NO , S.PAYOR_OFFICE_CODE , " 
    	+" CASE WHEN S.ADMISSION_TYPE_CODE = 'I' THEN 'IPD' ELSE 'OPD' END AS ADMISSION_TYPE_CODE, "
    	+" S.TRANSACTION_TYPE , S.TRANSACTION_MODULE , S.RECEIPT_TYPE_CODE , "
    	+" S.DOCTOR_CODE , 1 AS RECR ," 
        // +"CASE WHEN (S.FIRST_DEPARTMENT_CODE = '' OR S.FIRST_DEPARTMENT_CODE IS NULL) "
		//+"THEN S.DOCTOR_DEPARTMENT_CODE ELSE S.FIRST_DEPARTMENT_CODE "
    	//+"END AS FIRST_DEPARTMENT_CODE, "
    	+"S.ORDER_ITEM_ACTIVE, S.ACTIVE "
    	+"FROM TRN_DAILY S " 
    	+"LEFT OUTER JOIN DOCTOR D ON S.DOCTOR_CODE = D.CODE AND S.HOSPITAL_CODE = D.HOSPITAL_CODE "
    	+"LEFT OUTER JOIN ORDER_ITEM OI ON S.ORDER_ITEM_CODE = OI.CODE AND S.HOSPITAL_CODE = OI.HOSPITAL_CODE "
    	+"WHERE (S.INVOICE_DATE BETWEEN '"+this.from_date+"' AND '"+this.to_date+"') AND "
    	+"S.TRANSACTION_MODULE LIKE '"+this.transaction_module+"' AND "
    	+"S.ADMISSION_TYPE_CODE LIKE '"+this.admission_type+"' AND "
    	+"S.TRANSACTION_TYPE LIKE '"+this.transaction_type+"' AND "
    	+"S.DOCTOR_CODE LIKE '"+this.doctor_code+"' AND "
    	+"D.DOCTOR_CATEGORY_CODE LIKE '"+this.doctor_category+"' AND "
    	//+"D.DOCTOR_PROFILE_CODE LIKE '"+this.doctor_profile_code+"' AND "
    	+"S.HN_NO LIKE '"+this.hn_no+"' AND "
    	+"S.ORDER_ITEM_CODE LIKE '"+this.order_item+"' AND "
    	+"S.ORDER_ITEM_CATEGORY_CODE LIKE '"+this.order_item_category+"' AND "
    	+"S.INVOICE_NO LIKE '"+this.invoice_no+"' AND "
    	+"S.IS_WRITE_OFF LIKE '"+this.doc_type+"' AND "
    	+"S.HOSPITAL_CODE LIKE '"+this.hospital_code+"' "
    	+"ORDER BY S.ADMISSION_TYPE_CODE, S.HN_NO, S.INVOICE_NO, S.INVOICE_DATE, S.VERIFY_DATE";
    }
    
    private String importTransaction(){
        return "SELECT I.HOSPITAL_CODE, CASE WHEN I.ADMISSION_TYPE_CODE = 'I' THEN 'IPD' ELSE 'OPD' END AS ADMISSION_TYPE_CODE, "+
        "I.BILL_NO, I.BILL_DATE, I.LINE_NO, I.RECEIPT_TYPE_CODE, I.HN_NO, I.PATIENT_NAME, I.EPISODE_NO, I.PAYOR_CODE, "+
        "I.PAYOR_NAME, I.ORDER_ITEM_CODE, I.ORDER_ITEM_DESCRIPTION, I.DOCTOR_CODE, I.DOCTOR_NAME, I.AMOUNT_BEF_DISCOUNT, "+
        "I.AMOUNT_OF_DISCOUNT "+
        "FROM INT_HIS_BILL I LEFT OUTER JOIN ORDER_ITEM OI ON I.ORDER_ITEM_CODE = OI.CODE "+
        "WHERE (I.BILL_DATE BETWEEN '"+this.from_date+"' AND '"+this.to_date+"') AND "+
        "I.DOCTOR_CODE LIKE '"+this.doctor_code+"' AND "+
        "I.ADMISSION_TYPE_CODE LIKE '"+this.admission_type+"' AND "+
        "I.TRANSACTION_TYPE LIKE '"+this.transaction_type+"' AND "+
        "I.ORDER_ITEM_CODE LIKE '"+this.order_item+"' AND "+
        //"OI.ORDER_ITEM_CATEGORY_CODE LIKE '"+this.order_item_category+"' AND "+
        "I.HOSPITAL_CODE LIKE '"+this.hospital_code+"' "+
        "ORDER BY I.ADMISSION_TYPE_CODE, I.BILL_NO, I.LINE_NO";
    }
   
    private String summaryDaily(){
        return "SELECT S.HOSPITAL_CODE, CASE WHEN S.ADMISSION_TYPE_CODE = 'I' THEN 'IPD' ELSE 'OPD' END AS ADMISSION_TYPE_CODE, "+
        "S.TRANSACTION_TYPE, S.TRANSACTION_MODULE, S.INVOICE_NO, S.INVOICE_DATE,S.RECEIPT_TYPE_CODE, " +
        "S.HN_NO, S.LINE_NO, S.PATIENT_NAME, S.EPISODE_NO, S.PAYOR_OFFICE_CODE, S.PAYOR_OFFICE_NAME, " +
        "S.ORDER_ITEM_CODE, S.DOCTOR_CATEGORY_CODE, S.ORDER_ITEM_DESCRIPTION, S.DOCTOR_CODE, D.NAME_THAI, " +
        "S.AMOUNT_AFT_DISCOUNT, S.DOCTOR_DEPARTMENT_CODE, S.NOR_ALLOCATE_AMT, S.DR_AMT, S.HP_AMT, " +
        "S.DR_TAX_400+S.DR_TAX_401+S.DR_TAX_402+S.DR_TAX_406 AS DR_TAX_AMT, S.TAX_TYPE_CODE, S.NOR_ALLOCATE_PCT "+
        "FROM TRN_DAILY S LEFT OUTER JOIN DOCTOR D ON S.DOCTOR_CODE = D.CODE "+
        "LEFT OUTER JOIN ORDER_ITEM OI ON S.ORDER_ITEM_CODE = OI.CODE "+
        "WHERE ((S.INVOICE_DATE BETWEEN '"+from_date+"' AND '"+to_date+"') or (S.RECEIPT_DATE BETWEEN '"+from_date+"' AND '"+to_date+"')) AND "+
        "S.TRANSACTION_MODULE LIKE '"+transaction_module+"' AND "+
        "S.ADMISSION_TYPE_CODE LIKE '"+admission_type+"' AND "+
        "S.TRANSACTION_TYPE LIKE '"+transaction_type+"' AND "+
        "D.DOCTOR_PROFILE_CODE LIKE '"+doctor_profile_code+"' AND "+
        "S.DOCTOR_CODE LIKE '"+doctor_code+"' AND "+
        "D.DEPARTMENT_CODE LIKE '"+doctor_department+"' AND "+
        "D.DOCTOR_CATEGORY_CODE LIKE '"+this.doctor_category+"' AND "+
        "S.ORDER_ITEM_CODE LIKE '"+order_item+"' AND "+
        "OI.ORDER_ITEM_CATEGORY_CODE LIKE '"+order_item_category+"' AND "+
        "S.INVOICE_NO LIKE '"+invoice_no+"' AND "+
        "S.IS_WRITE_OFF LIKE '"+doc_type+"' AND "+
        "S.ACTIVE = '1' AND "+
        "S.HOSPITAL_CODE LIKE '"+hospital_code+"' "+
        "ORDER BY S.ADMISSION_TYPE_CODE, S.INVOICE_NO, S.LINE_NO";
    } 
  
    /**
     * TODO BY : Mr.sarunyoo keawsopa
     * DATE Modify :  26 January 2012
     * Status :  complete
     * @return String
     */
    private String revenueDetail(){
    	return " (SELECT  SD.YYYY AS 'YYYY' , SD.MM AS 'MM'  ,  "+
    			"SD.DOCTOR_CODE AS 'DOCTOR_CODE', "+
    			"DR.NAME_THAI AS 'NAME_THAI', "+
    			"SD.INVOICE_NO AS 'INVOICE_NO', "+
    			"SD.INVOICE_DATE AS 'INVOICE_DATE', "+
    			"SD.HN_NO AS 'HN_NO', "+
    			"SD.PATIENT_NAME AS 'PATIENT_NAME', "+
    			"OI.DESCRIPTION_THAI AS 'ORDER_ITEM', "+
    			"SD.VERIFY_DATE AS 'VERIFY_DATE', "+			
    			"SD.VERIFY_TIME AS 'START_TIME', "+
    			"SD.AMOUNT_AFT_DISCOUNT AS 'OLD_AMOUNT', "+
    			
    			// DR_AMT
    			"CASE WHEN SD.MM = '"+this.month+"' "+
    		 	"	THEN "+
    			"	SD.DR_AMT "+
    		 	"	ELSE "+
    			"	CASE WHEN SD.GUARANTEE_TERM_MM = '"+this.month+"' AND SD.GUARANTEE_TERM_YYYY = '"+this.year+"' "+ 
    			"	     AND SD.GUARANTEE_NOTE IN ('ABSORB SOME GUARANTEE','ABSORB OLD SOME') "+
    			"		THEN SD.GUARANTEE_PAID_AMT "+
    			"	 	ELSE SD.DR_AMT "+
    			"	END "+
    			"END AS 'DR_AMT', "+ // end DR_AMT
    			
    			"SD.GUARANTEE_NOTE, "+
    			"1 AS 'TYPE_QUERY', "+
    			"'' AS 'END_DATE', "+
    			"'' AS 'END_TIME', "+
    			"0.00 AS 'ABSORB_AMOUNT', "+
    			"SD.INVOICE_TYPE, "+
    			"SD.LINE_NO AS 'LINE_NO', "+
    			"DR.DOCTOR_CATEGORY_CODE, "+
    			"OI.ORDER_ITEM_CATEGORY_CODE, "+
    			"SD.DOCTOR_DEPARTMENT_CODE, "+ 
    			"SD.ORDER_ITEM_CODE, "+
    			"OI.ACCOUNT_CODE, "+
    			"SD.FIRST_LOCATION_CODE, " +
        		"SD.PATIENT_LOCATION_CODE, " +
        		"SD.RECEIPT_LOCATION_CODE, " +
        		"DP.CODE AS PROFILE_CODE , " +
    			"DP.NAME_THAI AS PROFILE_NAME, "+
    			"SD.IS_PAID, "+
    			"SD.IS_WRITE_OFF AS 'IS_WRITE_OFF', "+
    			"CASE WHEN SD.PAY_BY_AR = 'Y' THEN 'A' ELSE 'B' END AS PAY_METHOD, "+
    			"SD.AMOUNT_BEF_DISCOUNT-SD.AMOUNT_OF_DISCOUNT AS 'WRITE_OFF_AMOUNT', "+
   
    			//"CASE WHEN (SD.FIRST_DEPARTMENT_CODE = '' OR SD.FIRST_DEPARTMENT_CODE IS NULL) " +
        		//"THEN SD.DOCTOR_DEPARTMENT_CODE ELSE SD.FIRST_DEPARTMENT_CODE " +
        		//"END AS FIRST_DEPARTMENT_CODE, " +
        		
    			" SD.OLD_DR_AMT , " +
    			"CASE WHEN SD.MM = '"+this.month+"' "+
    		 	"     THEN SD.DR_TAX_406 "+
    		 	"     ELSE CASE WHEN SD.GUARANTEE_TERM_MM = '"+this.month+"' AND SD.GUARANTEE_TERM_YYYY = '"+this.year+"' "+
    			"		    AND SD.GUARANTEE_NOTE IN ('ABSORB OLD SOME','ABSORB SOME GUARANTEE') "+
    			"		    AND SD.DR_AMT > 0 "+
    			"	       THEN SD.GUARANTEE_PAID_AMT "+
    			"	       ELSE SD.DR_TAX_406 END "+
    			"     END AS DR_TAX_AMT"+
    		
    		"FROM TRN_DAILY SD LEFT OUTER JOIN DOCTOR DR ON (SD.DOCTOR_CODE = DR.CODE "+
    		"AND SD.HOSPITAL_CODE = DR.HOSPITAL_CODE) "+
    		"LEFT OUTER JOIN DOCTOR_PROFILE DP ON (DR.DOCTOR_PROFILE_CODE = DP.CODE "+
    		"AND DR.HOSPITAL_CODE = DP.HOSPITAL_CODE) "+
    		"LEFT OUTER JOIN ORDER_ITEM OI ON (OI.CODE = SD.ORDER_ITEM_CODE "+
    		"AND OI.HOSPITAL_CODE = SD.HOSPITAL_CODE) "+
    		"WHERE (DP.CODE BETWEEN '"+this.doctor_profile_code+"' AND '"+this.doctor_profile_code_to+"') "+
    		"AND SD.DOCTOR_CODE IN (SELECT DOCTOR_CODE FROM PAYMENT_MONTHLY WHERE YYYY = '"+this.year+"' "+
    		"AND MM = '"+this.month+"') "+
    		"AND (SD.YYYY LIKE '"+this.year+"' OR SD.GUARANTEE_TERM_YYYY LIKE '"+this.year+"') "+
    		"AND (SD.MM LIKE '"+this.month+"' OR (SD.GUARANTEE_TERM_MM LIKE '"+this.month+"' " +
    		"AND SD.GUARANTEE_NOTE LIKE 'ABS%')) "+
    		"AND SD.HOSPITAL_CODE LIKE '"+this.hospital_code+"' "+
    		"AND DR.DOCTOR_CATEGORY_CODE LIKE '"+this.doctor_category+"' "+
    		"AND DR.DEPARTMENT_CODE LIKE '"+this.doctor_department+"' "+
    		"AND SD.ACTIVE = '1' AND SD.ORDER_ITEM_ACTIVE = '1' "+
    		"AND OI.IS_COMPUTE = 'Y' "+
    		"AND SD.ORDER_ITEM_CODE LIKE '"+this.order_item+"' "+
    		"AND OI.ORDER_ITEM_CATEGORY_CODE LIKE '"+this.order_item_category+"' "+
    		"AND SD.COMPUTE_DAILY_DATE is not null AND SD.COMPUTE_DAILY_DATE != '' "+
    		"AND SD.AMOUNT_AFT_DISCOUNT > 0 "+
    		"AND (SD.GUARANTEE_NOTE LIKE '%OLD%' OR SD.DR_AMT > 0) )"+
    	"UNION "+
    		"(SELECT top 3000 "+
    			"'' AS 'YYYY', "+
    			"'' AS 'MM', "+
    			"STP.GUARANTEE_DR_CODE AS 'DOCTOR_CODE', "+
    			"DR.NAME_THAI AS 'NAME_THAI', "+
    			"'' AS 'INVOICE_NO', "+
    			"'' AS 'INVOICE_DATE', "+
    			"'' AS 'HN_NO', "+
    			"'' AS 'PATIENT_NAME', "+
    			"'' AS 'ORDER_ITEM', "+
    			"STP.START_DATE AS 'VERIFY_DATE', "+ 			
    			"STP.START_TIME AS 'START_TIME', "+
    			"0.00 AS 'OLD_AMOUNT', "+
    			"0.00 AS 'DR_AMT', "+
    			"'' AS 'GUARANTEE_NOTE', "+
    			"2 AS 'TYPE_QUERY', "+
    			"STP.END_DATE AS 'END_DATE', "+
    			"STP.END_TIME AS 'END_TIME', "+
    			"CASE WHEN HP402_ABSORB_AMOUNT > 0 "+
    			"	THEN (HP402_ABSORB_AMOUNT) "+
    			"	ELSE DF402_CASH_AMOUNT "+
    			"END AS 'ABSORB_AMOUNT', "+
    			"CASE WHEN STP.GUARANTEE_EXCLUDE_AMOUNT > 0 "+
    			"	THEN STP.GUARANTEE_TYPE_CODE+' : Extra Amount' "+
    			"	ELSE STP.GUARANTEE_TYPE_CODE+' : Absorb Guarantee' "+
    			"END AS 'INVOICE_TYPE', "+
    			"STP.GUARANTEE_TYPE_CODE AS 'LINE_NO', "+
    			"'' AS 'DOCTOR_CATEGORY_CODE', "+
    			"'' AS 'ORDER_ITEM_CATEGORY_CODE', "+
    			"DR.DEPARTMENT_CODE, "+
    			"'' AS 'ORDER_ITEM_CODE', "+
    			"'501301' AS 'ACCOUNT_CODE', "+
    			"'' AS 'FIRST_LOCATION_CODE', " +
        		"'' AS 'PATIENT_LOCATION_CODE', " +
        		"'' AS 'RECEIPT_LOCATION_CODE', " +
    			"DP.CODE AS PROFILE_CODE, "+
    			"DP.NAME_THAI AS PROFILE_NAME, "+
    			"'' AS 'IS_PAID', "+
    			"0.00 AS 'WRITE_OFF_AMOUNT', "+    
    			"'B' AS 'PAY_METHOD', "+
    			"'N' AS 'IS_WRITE_OFF', "+
    			"0.00 AS 'OLD_DR_AMT' , "+	
    			"CASE WHEN HP402_ABSORB_AMOUNT > 0 "+
    			"	THEN (HP402_ABSORB_AMOUNT) "+
    			"	ELSE DF402_CASH_AMOUNT "+
    			"END AS 'DR_TAX_AMT' "+
                //"'' AS 'FIRST_DEPARTMENT_CODE', " +
    		"FROM STP_GUARANTEE STP "+
    		"LEFT OUTER JOIN DOCTOR DR ON (STP.GUARANTEE_DR_CODE = DR.CODE AND STP.HOSPITAL_CODE = DR.HOSPITAL_CODE) "+
    		"LEFT OUTER JOIN DOCTOR_PROFILE DP ON (DR.DOCTOR_PROFILE_CODE = DP.CODE AND "+
    		"DR.HOSPITAL_CODE = DP.HOSPITAL_CODE) "+
    		"WHERE (STP.DF402_CASH_AMOUNT+STP.HP402_ABSORB_AMOUNT+STP.DF406_HOLD_AMOUNT >0) "+
    		"AND STP.GUARANTEE_TYPE_CODE = 'DLY' "+
    		"AND (DR.DOCTOR_PROFILE_CODE BETWEEN '"+this.doctor_profile_code+"' AND '"+this.doctor_profile_code_to+"') "+
    		"AND STP.YYYY LIKE '"+this.year+"' "+
    		"AND STP.MM LIKE '"+this.month+"' "+
    		"AND STP.HOSPITAL_CODE LIKE '"+this.hospital_code+"' "+
    		"AND DR.DOCTOR_CATEGORY_CODE LIKE '"+this.doctor_category+"' "+
    		"AND DR.DEPARTMENT_CODE LIKE '"+this.doctor_department+"') "+
    	"UNION "+
    		"(SELECT DISTINCT " +
    		"	'' AS 'YYYY', "+
    		"	'' AS 'MM', "+
    		"	STP.GUARANTEE_DR_CODE AS 'DOCTOR_CODE', "+
    		"	DR.NAME_THAI AS 'NAME_THAI', "+
    		"	'' AS 'INVOICE_NO', "+
    		"	'' AS 'INVOICE_DATE', "+
    		"	'' AS 'HN_NO', "+
    		"	'' AS 'PATIENT_NAME', "+
    		"	'' AS 'ORDER_ITEM', "+  
    		"	STP.YYYY+STP.MM+'01' AS 'VERIFY_DATE', "+
    		"	'' AS 'START_TIME', "+
    		"	0.00 AS 'OLD_AMOUNT', "+
    		"	0.00 AS 'DR_AMT', "+
    		"	'' AS 'GUARANTEE_NOTE', "+
    		"	2 AS 'TYPE_QUERY', "+
    		"	STP.YYYY+STP.MM+'01' AS 'END_DATE', "+
    		"	'' AS 'END_TIME', "+
    		"	CASE WHEN HP402_ABSORB_AMOUNT > 0 "+
    		"		THEN (HP402_ABSORB_AMOUNT) "+
    		"		ELSE DF402_CASH_AMOUNT "+
    		"	END AS 'ABSORB_AMOUNT', "+ // ABSORB_AMOUNT
    		"	CASE WHEN STP.GUARANTEE_EXCLUDE_AMOUNT > 0 "+
    		"		THEN STP.GUARANTEE_TYPE_CODE+' : Extra Amount' "+
    		"		ELSE STP.GUARANTEE_TYPE_CODE+' : Absorb Guarantee' "+
    		"	END AS 'INVOICE_TYPE', "+ // INVOICE_TYPE
    		"	STP.GUARANTEE_TYPE_CODE AS 'LINE_NO', "+
    		"	'' AS 'DOCTOR_CATEGORY_CODE', "+
    		"	'' AS 'ORDER_ITEM_CATEGORY_CODE', "+
    		"   DR.DEPARTMENT_CODE, "+
    		"   '' AS 'ORDER_ITEM_CODE', "+
    		"   '501301' AS 'ACCOUNT_CODE', "+
    		"   '' AS 'FIRST_LOCATION_CODE', " +
    		"   '' AS 'PATIENT_LOCATION_CODE', " +
    		"   '' AS 'RECEIPT_LOCATION_CODE', " +
    		"	DP.CODE AS PROFILE_CODE, "+
    		"	DP.NAME_THAI AS PROFILE_NAME, "+ 
    		"	'' AS 'IS_PAID', "+
    		"	'N' AS 'IS_WRITE_OFF', "+
    		"	'B' AS 'PAY_METHOD', "+
    		"	0.00 AS 'WRITE_OFF_AMOUNT', "+			
    		"	0.00 AS 'OLD_DR_AMT' ,"+
    		"	CASE WHEN HP402_ABSORB_AMOUNT > 0 "+
    		"		THEN (HP402_ABSORB_AMOUNT) "+
    		"		ELSE DF402_CASH_AMOUNT "+
    		"	END AS 'DR_TAX_AMT' " + // DR_TAX_AMT
            //"   '' AS 'FIRST_DEPARTMENT_CODE', " +
    		"FROM STP_GUARANTEE STP "+
    		"LEFT OUTER JOIN DOCTOR DR ON (STP.GUARANTEE_DR_CODE = DR.CODE AND STP.HOSPITAL_CODE = DR.HOSPITAL_CODE) "+
    		"LEFT OUTER JOIN DOCTOR_PROFILE DP ON (DR.DOCTOR_PROFILE_CODE = DP.CODE AND "+
    		"DR.HOSPITAL_CODE = DP.HOSPITAL_CODE) "+
    		"WHERE (STP.DF402_CASH_AMOUNT+STP.HP402_ABSORB_AMOUNT+STP.DF406_HOLD_AMOUNT >0) "+
    		"AND STP.GUARANTEE_TYPE_CODE <> 'DLY' "+
    		"AND (DR.DOCTOR_PROFILE_CODE BETWEEN '"+this.doctor_profile_code+"' AND '"+this.doctor_profile_code_to+"') "+
    		"AND STP.YYYY LIKE '"+this.year+"' "+
    		"AND STP.MM LIKE '"+this.month+"' "+
    		"AND STP.HOSPITAL_CODE LIKE '"+this.hospital_code+"' "+
    		"AND DR.DOCTOR_CATEGORY_CODE LIKE '"+this.doctor_category+"' "+
    		"AND DR.DEPARTMENT_CODE LIKE '"+this.doctor_department+"') "+
    	"ORDER BY PROFILE_CODE,DOCTOR_CODE,PAY_METHOD,VERIFY_DATE,TYPE_QUERY;";
    }

    /**
     * TODO BY : Mr.sarunyoo keawsopa
     * Date Modify : 27 January
     * Status : complete
     * @return String
     */
    private String unpaidDetail(){
    	return "SELECT "+
    	"SD.YYYY AS 'YYYY', "+
    	"SD.MM AS 'MM', "+
    	"SD.DOCTOR_CODE AS 'DOCTOR_CODE', "+
    	"DR.NAME_THAI AS 'NAME_THAI', "+
    	"SD.INVOICE_NO AS 'INVOICE_NO', "+
    	"SD.INVOICE_DATE AS 'INVOICE_DATE', "+
    	"SD.VERIFY_DATE AS 'VERIFY_DATE', "+
    	"SD.VERIFY_TIME AS 'START_TIME', "+
    	"SD.HN_NO AS 'HN_NO', "+
    	"SD.PATIENT_NAME AS 'PATIENT_NAME', "+
    	"OI.DESCRIPTION_THAI AS 'DESCRIPTION_THAI', "+
    	"SD.AMOUNT_AFT_DISCOUNT, "+
    	"SD.DR_AMT AS 'DR_AMT', "+
    	"SD.GUARANTEE_PAID_AMT, "+
    	"CASE WHEN SD.GUARANTEE_NOTE IS NULL THEN '' ELSE SD.GUARANTEE_NOTE END AS GUARANTEE_NOTE, "+
    	"SD.AMOUNT_BEF_DISCOUNT - SD.AMOUNT_OF_DISCOUNT AS 'OLD_AMOUNT', "+
    	"SD.DR_TAX_406 AS 'DR_TAX_AMT', "+
    	"DP.CODE AS PROFILE_CODE, "+
    	"DP.NAME_THAI AS PROFILE_NAME, "+
    	"DR.DOCTOR_CATEGORY_CODE AS 'DOCTOR_CATEGORY_CODE' , "+
    	"SD.ORDER_ITEM_CODE AS 'ORDER_ITEM_CODE', "+
    	"SD.DOCTOR_DEPARTMENT_CODE, "+
    	"SD.ORDER_ITEM_CATEGORY_CODE, "+
    	"OI.ACCOUNT_CODE, "+
    	"SD.FIRST_LOCATION_CODE, " +
		"SD.PATIENT_LOCATION_CODE, " +
		"SD.RECEIPT_LOCATION_CODE, " +
		"SD.PAYOR_OFFICE_CODE, "+
    	"SD.PAYOR_OFFICE_NAME, "+
    	"SD.IS_PAID, "+
    	"SD.ADMISSION_TYPE_CODE AS 'ADMISSION_TYPE_CODE', "+
    	"SD.TRANSACTION_MODULE AS 'TRANSACTION_MODULE', "+
    	"SD.LINE_NO AS 'LINE_NO' "+
        //"CASE WHEN (SD.FIRST_DEPARTMENT_CODE = '' OR SD.FIRST_DEPARTMENT_CODE IS NULL) " +
		//"THEN SD.DOCTOR_DEPARTMENT_CODE ELSE SD.FIRST_DEPARTMENT_CODE " +
		//"END AS FIRST_DEPARTMENT_CODE, " +
    	"FROM TRN_DAILY SD LEFT OUTER JOIN DOCTOR DR ON (SD.DOCTOR_CODE = DR.CODE "+
    	"AND SD.HOSPITAL_CODE = DR.HOSPITAL_CODE) "+
    	"LEFT OUTER JOIN DOCTOR_PROFILE DP ON (DR.DOCTOR_PROFILE_CODE = DP.CODE "+
    	"AND DR.HOSPITAL_CODE = DP.HOSPITAL_CODE) "+
    	"LEFT OUTER JOIN ORDER_ITEM OI ON (OI.CODE = SD.ORDER_ITEM_CODE "+
    	"AND OI.HOSPITAL_CODE = SD.HOSPITAL_CODE) "+
    	"WHERE (SD.INVOICE_DATE BETWEEN '"+this.from_date+"' AND '"+this.to_date+"') AND "+
    	"DP.CODE LIKE '"+this.doctor_profile_code+"' AND "+
    	"DR.DEPARTMENT_CODE LIKE '"+this.doctor_department+"' AND "+
    	"SD.PAYOR_OFFICE_CODE LIKE '"+this.payor_code+"' AND "+
    	"SD.ACTIVE = '1' AND DR.ACTIVE = '1' AND SD.ORDER_ITEM_ACTIVE = '1' AND "+
    	"SD.YYYY = '' AND SD.MM = '' AND "+
    	"(SD.BATCH_NO = '' OR SD.BATCH_NO IS NULL) AND "+
    	"SD.INVOICE_TYPE <> 'ORDER' AND "+
    	"SD.HOSPITAL_CODE LIKE '"+this.hospital_code+"' "+
    	"ORDER BY PROFILE_CODE,DOCTOR_CODE,INVOICE_DATE";
    }
   
    private String sumRevenue(){
    	return "SELECT SM.YYYY, SM.MM, SM.DOCTOR_CODE, DR.NAME_THAI, SM.SUM_AMT, SM.DR_SUM_AMT, "+
    	"SM.DR_TAX_400, SM.DR_TAX_401, SM.DR_TAX_402, SM.DR_TAX_406, SM.DR_NET_TAX_406_AMT, "+
    	"CASE WHEN SM.PAYMENT_MODE_CODE = 'B' THEN 'BANK TRANSFER' ELSE 'CHEQUE' END AS PAYMENT_MODE_CODE, "+
    	"'' AS DESCRIPTION, SM.DR_NET_PAID_AMT, SM.EXDR_AMT, "+
    	"SM.EXCR_AMT, PM.SUM_TAX_402 AS GDR_402, PM.SUM_TAX_406 AS GDR_406, "+
    	"PM.GUARANTEE_AMOUNT, "+
    	"SM.REF_PAID_NO, DP.CODE, DP.NAME_THAI AS PROFILE_NAME, "+
    	"CASE WHEN DR.HOSPITAL_UNIT_CODE = '' THEN 'UNKNOW' ELSE DR.HOSPITAL_UNIT_CODE END AS HOSPITAL_UNIT_CODE, "+
    	"HU.DESCRIPTION AS H_UNIT "+
    	"FROM PAYMENT_MONTHLY SM "+
    	"LEFT OUTER JOIN DOCTOR DR ON (SM.DOCTOR_CODE = DR.CODE AND "+
    	"SM.HOSPITAL_CODE = DR.HOSPITAL_CODE) "+
    	"LEFT JOIN SUMMARY_GUARANTEE PM ON (SM.DOCTOR_CODE = PM.DOCTOR_CODE AND "+
    	"SM.YYYY+SM.MM = PM.YYYY+PM.MM) "+
    	"LEFT OUTER JOIN DOCTOR_PROFILE DP ON (DR.DOCTOR_PROFILE_CODE = DP.CODE AND "+
    	"DR.HOSPITAL_CODE = DP.HOSPITAL_CODE) "+
    	"LEFT OUTER JOIN HOSPITAL_UNIT HU ON DR.HOSPITAL_UNIT_CODE = HU.CODE "+
    	"AND DR.HOSPITAL_CODE = HU.HOSPITAL_CODE "+
    	"WHERE SM.YYYY like '"+this.year+"' "+
    	"AND SM.MM like '"+this.month+"' "+
    	"AND (SM.SUM_AMT > 0 OR PM.GUARANTEE_AMOUNT > 0 OR SM.EX_406 > 0) "+
    	"AND SM.PAYMENT_MODE_CODE LIKE '"+this.payment_mode+"' "+
    	"AND DR.DOCTOR_TYPE_CODE LIKE '"+this.doctor_type+"' "+
    	"AND SM.HOSPITAL_CODE LIKE '"+this.hospital_code+"' "+
    	"ORDER BY SM.PAYMENT_MODE_CODE,DR.HOSPITAL_UNIT_CODE, DP.CODE, SM.DOCTOR_CODE";
    }
}